package org.company.venus.compare;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.company.earth.util.Pair;

public class TableCompare {
    
    private static Logger logger = Logger.getLogger(TableCompare.class);
    
    public static class MetaColumn {
        
        public final static String TABLE_NAME = "TABLE_NAME";
        public final static String COLUMN_NAME = "COLUMN_NAME";
        public final static String TYPE_NAME = "TYPE_NAME";
        public final static String DATA_TYPE = "DATA_TYPE";
        public final static String COLUMN_SIZE = "COLUMN_SIZE";
        public final static String DECIMAL_DIGITS = "DECIMAL_DIGITS";
        public final static String COLUMN_DEF = "COLUMN_DEF";
        public final static String REMARKS = "REMARKS";
        public final static String NULLABLE = "NULLABLE";
        
        
        private String tableName;
        private String columnName;
        private String typeName;
        private Integer dataType;
        private Integer columnSize;
        private Integer decimalDigits;
        private String comment;
        private String defValue;
        private Integer isNull;
        
        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getTypeName() {
            return typeName;
        }

        public void setTypeName(String typeName) {
            this.typeName = typeName;
        }

        public Integer getDataType() {
            return dataType;
        }

        public void setDataType(Integer dataType) {
            this.dataType = dataType;
        }

        public Integer getColumnSize() {
            return columnSize;
        }

        public void setColumnSize(Integer columnSize) {
            this.columnSize = columnSize;
        }
        
        public Integer getDecimalDigits() {
            return decimalDigits;
        }

        public void setDecimalDigits(Integer decimalDigits) {
            this.decimalDigits = decimalDigits;
        }

        public String getComment() {
            return comment;
        }

        public void setComment(String comment) {
            this.comment = comment;
        }

        public String getDefValue() {
            return defValue;
        }

        public void setDefValue(String defValue) {
            this.defValue = defValue;
        }

        public Integer getIsNull() {
            return isNull;
        }

        public void setIsNull(Integer isNull) {
            this.isNull = isNull;
        }

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public MetaColumn(ResultSet set) throws SQLException {
            this.tableName = set.getString(TABLE_NAME);
            this.columnName = set.getString(COLUMN_NAME);
            this.typeName = set.getString(TYPE_NAME);
            this.dataType = set.getInt(DATA_TYPE);
            this.columnSize = set.getInt(COLUMN_SIZE);
            this.decimalDigits = set.getInt(DECIMAL_DIGITS);
            this.comment = set.getString(REMARKS);
            this.defValue = set.getString(COLUMN_DEF);
            this.isNull = set.getInt(NULLABLE);
        }
        
        @Override
        public boolean equals(Object obj) {
            MetaColumn target = (MetaColumn)obj;
            if(!this.getTableName().equalsIgnoreCase(target.getTableName())) {
                return false;
            }
            if(!this.getColumnName().equalsIgnoreCase(target.getColumnName())) {
                return false;
            }
            return true;
        }
        
        /**
         * 是否是日期类型
         * @param origin
         * @param target
         * @return
         */
        public boolean isDateTime(int dataType) {
            if(dataType == 7 || dataType == 93) {
                return true;
            }
            return false;
        }
        
        public boolean dataTypeEquals(int origin,int target) {
            if(origin == target) {
                return true;
            }
            
            return false;
        }
        
        public boolean same(Object obj) {
            MetaColumn target = (MetaColumn)obj;
            if(!StringUtils.equalsIgnoreCase(this.getTableName(), target.getTableName())) {
                return false;
            }
            if(!StringUtils.equalsIgnoreCase(this.getColumnName(), target.getColumnName())) {
                return false;
            }
            if(!StringUtils.equalsIgnoreCase(this.getDefValue(), target.getDefValue())) {
                return false;
            }
            if(this.isNull != target.isNull){
                return false;
            }
            if(!StringUtils.endsWithIgnoreCase(this.comment, target.comment)) {
                return false;
            }
            //日期类型单独处理
            if(this.isDateTime(this.getDataType()) && this.isDateTime(target.getDataType())) {
                return true;
            }
            if(!this.getDataType().equals(target.getDataType())) {
                return false;
            }
            if(!this.getColumnSize().equals(target.getColumnSize())) {
                return false;
            }
            if(!this.getDecimalDigits().equals(target.getDecimalDigits())) {
                return false;
            }
            return true;
        }
        
        @Override
        public String toString() {
            // TODO Auto-generated method stub
            StringBuffer sb = new StringBuffer();
            sb.append(this.tableName + ",\t");
            sb.append(this.columnName + ",\t");
            sb.append(this.typeName + ",\t");
            sb.append(this.dataType + ",\t");
            sb.append(this.columnSize + ",\t");
            sb.append(this.decimalDigits + ",\t");
            sb.append(this.isNull + ",\t");
            sb.append(this.defValue + ",\t");
            sb.append(this.comment + ",\t");
            return sb.toString();
        }
    }
    
    private List<MetaColumn> originList;
    private List<MetaColumn> targetList;
    private List<Pair<MetaColumn>> pairList;
    
    public TableCompare () {
        originList = new ArrayList<MetaColumn>();
        targetList = new ArrayList<MetaColumn>();
        pairList = new ArrayList<Pair<MetaColumn>>();
    }
    
    protected void transfer(ResultSet origin,ResultSet target) throws SQLException {
        MetaColumn meta = null;
        while(origin.next()) {
            meta = new MetaColumn(origin);
            originList.add(meta);
        }
        
        while(target.next()) {
            meta = new MetaColumn(target);
            targetList.add(meta);
        }
        
        Iterator<MetaColumn> originIter = originList.iterator();
        Iterator<MetaColumn> targetIter = null;
        MetaColumn first,second;
        Pair<MetaColumn> pair = null;
        while(originIter.hasNext()) {
            targetIter = targetList.iterator();
            first = originIter.next();
            while(targetIter.hasNext()) {
                second = targetIter.next();
                if(first.same(second)) {
                    pair = new Pair<TableCompare.MetaColumn>(first, second);
                    pairList.add(pair);
                    targetIter.remove();
                    originIter.remove();
                    break;
                }
            }
        }
    }
    
    
    protected Boolean compare(ResultSet tar,ResultSet org,String tableName) throws SQLException {
        List<MetaColumn> first = new ArrayList<TableCompare.MetaColumn>();
        List<MetaColumn> second = new ArrayList<TableCompare.MetaColumn>();
        MetaColumn columns = null;
        while(tar.next()) {
            columns = new MetaColumn(tar);
            first.add(columns);
        }
        
        while(org.next()) {
            columns = new MetaColumn(org);
            second.add(columns);
        }
        
        Iterator<MetaColumn> tarIter = null;
        tarIter = first.iterator();
        MetaColumn target = null;
        
        while(tarIter.hasNext()) {
            target = tarIter.next();
            if(second.contains(target)) {
                tarIter.remove();
                second.remove(target);
            }
        }
        
        List<MetaColumn> retainList = new ArrayList<TableCompare.MetaColumn>(first);
        retainList.retainAll(second);
        this.targetList.removeAll(retainList);
        this.originList.removeAll(retainList);
        if(this.targetList.isEmpty() && this.originList.isEmpty()) {
            return true;
        }
        return false;
    }
    
    private void loggerColumn() {
        final String MODIFY = "MMMM %s origin:%s\t||\ttarget:%s";
        final String ADD = "++++ %s\torigin:%s";
        final String MINUS = "---- %s\ttarget:%s";
        for(Pair<MetaColumn> pair : pairList) {
            if(!pair.first.equals(pair.second)) {
                System.out.println(String.format(MODIFY, pair.first.getTableName(),pair.first.toString(),pair.second.toString()));
            }
        }
        
        for(MetaColumn meta : this.originList) {
            System.out.println(String.format(ADD, meta.getTableName(),meta.toString()));
        }
        
        for(MetaColumn meta : this.targetList) {
            System.out.println(String.format(MINUS, meta.getTableName(),meta.toString()));
        }
    }
    
    private void loggerTable(List<String> origin,List<String> target) {
        final String ADD = "++++ origin:%s";
        final String MINUS = "---- target:%s";
        for(String tableName : origin) {
            System.out.println(String.format(ADD, tableName));
        }
        for(String tableName : target) {
            System.out.println(String.format(MINUS, tableName));
        }
    }
    
    public void compare(Connection origin,Connection target,String tableName) throws SQLException {
        DatabaseMetaData origin_meta = origin.getMetaData();
        DatabaseMetaData target_meta = target.getMetaData();
        ResultSet origin_set = origin_meta.getColumns(null, null, tableName, null);
        ResultSet target_set = target_meta.getColumns(null, null, tableName, null);
        this.transfer(origin_set, target_set);
        this.loggerColumn();
    }
    
    public void compare(Connection originCon,Connection targetCon,String[] tables) throws SQLException {
        TableCompare tableCompare = null;
        for(String tableName : tables) {
            tableCompare = new TableCompare();
            tableCompare.compare(originCon, targetCon, tableName);
        }
    }
    
    public List<String> getTables(Connection originCon) throws SQLException {
        List<String> tables = new ArrayList<String>();
        ResultSet set = originCon.getMetaData().getTables(null, null, null ,new String[]{"TABLE"});
        while(set.next()) {
            tables.add(set.getString(MetaColumn.TABLE_NAME));
        }
        return tables;
    }
    
    public List<String> getBaseTables(Connection originCon) throws SQLException {
        List<String> tables = this.getTables(originCon);
        List<String> baseTables = new ArrayList<String>();
        Iterator<String> iter = tables.iterator();
        String tableName = null;
        while(iter.hasNext()) {
            tableName = iter.next();
            if(tableName.startsWith("T_") || tableName.startsWith("t_")) {
                baseTables.add(tableName.toUpperCase());
            }
        }
        return baseTables;
    }
    
    public List<String> compareTables(Connection originCon,Connection targetCon) throws SQLException {
        List<String> originTables = this.getBaseTables(originCon);
        List<String> targetTabels = this.getBaseTables(targetCon);
        List<String> retainTables = new ArrayList<String>(originTables);
        retainTables.retainAll(targetTabels);
        originTables.removeAll(retainTables);
        targetTabels.removeAll(retainTables);
        loggerTable(originTables,targetTabels);
        return retainTables;
    }
}
